import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Table
import configuration
from sqlalchemy import desc


Base = declarative_base()

ENGINE_INFO = 'mysql+pymysql://' + configuration.DB_USER + ':' + configuration.DB_PASSWD + '@' + configuration.DB_HOST \
              + '/' + configuration.DB_DATABASE + '?charset=' + configuration.DB_CHARSET
ENGINE = sqlalchemy.create_engine(ENGINE_INFO)


class Database(object):
    def __init__(self):
        self.engine = ENGINE
        self.connection = None
        self.session = None

    def connect(self):
        self.connection = self.engine.connect()
        session = sessionmaker(bind=self.engine, autocommit=False)
        self.session = session()

    def close(self):
        self.session.commit()
        self.session.close()
        self.connection.close()


class ExceptionalEnterprise(Base):
    __table__ = Table(
        configuration.DB_TABLES['exceptional_enterprises'], Base.metadata, autoload=True, autoload_with=ENGINE
    )

    def __init__(self, ee):
        """

        :param ee: ExceptionalEnterprise object in objects.py
        """
        l = ee.__dict__
        for key, value in l.items():
            setattr(self, key, value)

    @classmethod
    def get_latest_time(cls):
        db = Database()
        db.connect()

        cursor = db.session.query(cls).order_by(desc(cls.listed_date)).limit(1).one()
        latest_date = cursor.listed_date

        db.close()
        return latest_date

    @classmethod
    def save(cls, ee_list):
        """

        :param ee_list: a list containing lots of ExceptionalEnterprise objects in objects.py
        :return:
        """
        db = Database()
        db.connect()

        for ee in ee_list:
            db.session.merge(cls(ee))

        db.close()


class ExceptionalReason(Base):
    __table__ = Table(
        configuration.DB_TABLES['exceptional_reasons'], Base.metadata, autoload=True, autoload_with=ENGINE
    )

    def __init__(self, er):
        """

        :param er: ExceptionalReason object in objects.py
        """
        l = er.__dict__
        for key, value in l.items():
            setattr(self, key, value)

    @classmethod
    def save(cls, er_list):
        """

        :param er_list: a list containing lots of ExceptionalReason objects in objects.py
        :return:
        """
        db = Database()
        db.connect()

        for er in er_list:
            db.session.merge(cls(er))

        db.close()

